# -*- coding:UTF-8 -*-

num = 0
f1 = open('gupiao_2019-05-31.sql', 'w')

f = open("/Users/colin/Downloads/gupiao_2019-05-31.sql", "r")
line = f.readline()
while line:
    line = f.readline()

    if line.find('# Dump of table') >= 0:
        line = line.replace("table 公司", "table fa_dahui_gongshi")
        line = line.replace("table 补缴", "table fa_dahui_bujiao")
        line = line.replace("table 订单", "table fa_dahui_dingdan")
        line = line.replace("table 工资", "table fa_dahui_gongzi")
        line = line.replace("table 客户", "table fa_dahui_kehu")
        line = line.replace("table 年月", "table fa_dahui_nianyue")
        line = line.replace("table 企业补缴", "table fa_dahui_qiyebujiao")
        line = line.replace("table 企业工资", "table fa_dahui_qiyegongzi")
        line = line.replace("table 企业工资系数", "table fa_dahui_qiyegongzixishu")
        line = line.replace("table 企业客户", "table fa_dahui_qiyekehu")
        line = line.replace("table 企业年月", "table fa_dahui_qiyenianyue")
        line = line.replace("table 企业普通", "table fa_dahui_qiyeputong")
        line = line.replace("table 业务", "table fa_dahui_yewu")

    if line.find('LOCK TABLES `') >= 0 or line.find('ALTER TABLE `') >= 0:
        line = line.replace("`公司`", "`fa_dahui_gongshi`")
        line = line.replace("`补缴`", "`fa_dahui_bujiao`")
        line = line.replace("`订单`", "`fa_dahui_dingdan`")
        line = line.replace("`工资`", "`fa_dahui_gongzi`")
        line = line.replace("`客户`", "`fa_dahui_kehu`")
        line = line.replace("`年月`", "`fa_dahui_nianyue`")
        line = line.replace("`企业补缴`", "`fa_dahui_qiyebujiao`")
        line = line.replace("`企业工资`", "`fa_dahui_qiyegongzi`")
        line = line.replace("`企业工资系数`", "`fa_dahui_qiyegongzixishu`")
        line = line.replace("`企业客户`", "`fa_dahui_qiyekehu`")
        line = line.replace("`企业年月`", "`fa_dahui_qiyenianyue`")
        line = line.replace("`企业普通`", "`fa_dahui_qiyeputong`")
        line = line.replace("`业务`", "`fa_dahui_yewu`")

    if line.find('INSERT INTO `补缴`') >= 0:
        line = "INSERT INTO `fa_dahui_bujiao` (`ordernumber`, `orderdate`, `overdueyear`, `businesstype`, `taobaoname`," \
               " `idnumber`, `paymentnumber`, `payingcompany`, `paymentbase`, `paymentamount`, `monthspaid`," \
               " `yearpayment`, `agencyfees`, `ordercomment`, `auditstatus`, `supplementarynote`, " \
               "`overduestatus`, `preprocessing`, `refundornot`)"

    if line.find('INSERT INTO `订单`') >= 0:
        line = "INSERT INTO `fa_dahui_dingdan` (`ordernumber`, `orderdate`, `businesstype`, `taobaoname`, " \
               "`idnumber`, `paymentnumber`, `payingcompany`, `paymentbase`, `paymentamount`, `paymentdate`," \
               " `agencyfees`, `ordercomment`, `auditstatus`, `newkehu`, `preprocessing`)"

    if line.find('INSERT INTO `公司`') >= 0:
        line = "INSERT INTO `fa_dahui_gongshi` (`businesstype`, `name`, `minimumcardinality`, `proportion`)"

    if line.find('INSERT INTO `工资`') >= 0:
        line = "INSERT INTO `fa_dahui_gongzi` (`ordernumber`, `orderdate`, `opyear`, `taobaoname`, `idnumber`," \
               " `payyear`, `agencyfees`, `auditstatus`, `paynote`, `paystatus`, `preprocessing`, " \
               "`refundornot`, `sbbase`, `gjjbase`, `totalwages`, `d_pension`, `d_yiliao`, `d_shiye`," \
               " `d_shengyu`, `d_gongshang`, `d_chanbao`, `d_gjj`, `d_heji`, `s_pension`, `s_yiliao`, `s_shiye`," \
               " `s_gjj`, `s_heji`, `y_wages`, `tax`, `r_wages`)"

    if line.find('INSERT INTO `客户`') >= 0:
        line = "INSERT INTO `fa_dahui_kehu` (`idnumber`, `name`, `mobile`, `bankcardnumber`, `note`)"

    if line.find('INSERT INTO `年月`') >= 0:
        line = "INSERT INTO `fa_dahui_nianyue` (`businessnumber`, `paymentdate`, `payingcompany`, `paymentbase`," \
               " `paymentamount`, `earlytransfer`, `newjoin_note`, `newjoin_status`, `zc_note`, `zc_status`," \
               " `refundornot`, `taobaoname`)"

    if line.find('INSERT INTO `企业补缴`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyebujiao` (`businessnumber`, `ownedcompany`, `overdueyear`, `businesstype`," \
               " `idnumber`, `paymentnumber`, `payingcompany`, `paymentbase`, `paymentamount`, " \
               "`monthspaid`, `yearpayment`, `supplementarynote`, `overduestatus`)"

    if line.find('INSERT INTO `企业工资`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyegongzi` (`ownedcompany`, `payyear`, `idnumber`, `paynote`, `paystatus`," \
               " `sbbase`, `gjjbase`, `basewages`, `bonuses`, `allowance`, `overtimepay`, `deductions`," \
               " `totalwages`, `d_pension`, `d_yiliao`, `d_shiye`, `d_shengyu`, `d_gongshang`, `d_chanbao`," \
               " `d_gjj`, `d_heji`, `s_pension`, `s_yiliao`, `s_shiye`, `s_gjj`, `s_heji`, `y_wages`, `tax`, " \
               "`r_wages1`, `subsidies`, `withholding`, `r_wages2`, `managementfee`, `totalamount`, " \
               "`year_end_award`, `year_end_award_tax`, `year_end_award_real`, `compensation`, " \
               "`compensation_tax`, `compensation_real`, `totalamount_z`)"

    if line.find('INSERT INTO `企业工资系数`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyegongzixishu` (`d_pension`, `d_yiliao1`, `d_yiliao2`, `d_shiye`, " \
               "`d_shengyu`, `d_gongshang`, `d_chanbao`, `d_gjj`, `s_pension`, `s_yiliao`, `s_shiye`, `s_gjj`)"

    if line.find('INSERT INTO `企业客户`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyekehu` (`idnumber`, `name`, `mobile`, `address0`, `address1`," \
               " `bankcardnumber`, `note`)"

    if line.find('INSERT INTO `企业年月`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyenianyue` (`businessnumber`, `yearpayment`, `payingcompany`," \
               " `paymentbase`, `paymentamount`, `note`, `type`, `status`)"

    if line.find('INSERT INTO `企业普通`') >= 0:
        line = "INSERT INTO `fa_dahui_qiyeputong` (`businessnumber`, `businesstype`, `idnumber`, " \
               "`paymentnumber`, `ownedcompany`, `contractperiod`)"

    if line.find('INSERT INTO `业务`') >= 0:
        line = "INSERT INTO `fa_dahui_yewu` (`businessnumber`, `businesstype`, `idnumber`, " \
               "`paymentnumber`, `agencyfees`)"

    f1.writelines(line)
    num += 1
    print('----finish---' + str(num))

f.close()
f1.close()

print('----OK---')

"""

truncate table fa_dahui_gongshi;
truncate table fa_dahui_bujiao;
truncate table fa_dahui_dingdan;
truncate table fa_dahui_gongzi;
truncate table fa_dahui_kehu;
truncate table fa_dahui_nianyue;
truncate table fa_dahui_qiyebujiao;
truncate table fa_dahui_qiyegongzi;
truncate table fa_dahui_qiyekehu;
truncate table fa_dahui_qiyenianyue;
truncate table fa_dahui_qiyeputong;
truncate table fa_dahui_yewu;


"""